﻿using Oracle.ManagedDataAccess.Client;
using Oracle.ManagedDataAccess.Types;
using System.ComponentModel.Design;
using System.Data;
using System.Security.Principal;
using static System.ComponentModel.Design.ObjectSelectorEditor;
using System.Windows.Forms;
using static System.Runtime.InteropServices.JavaScript.JSType;


namespace Bank_database_system
{

    public static class TransactionRecord
    {

        private const string connectionString = @"Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=47.96.39.153)(PORT=1521))(CONNECT_DATA=(SID=orcl)));
                                                 User Id=system;
                                                 Password=Tongji123456;
                                                 Connection Timeout=5;";
        public static string views = "";
        public static bool createInquiry(string card, string start, string end, string state, string type, string ID)
        {
            string describe = "";

            start += "  00:00:00";
            end += "  23:59:59";
            views = $"TRANSACTION_{card}";
            int i = 0;
            string p = $"SELECT COUNT(*) FROM USER_TABLES WHERE TABLE_NAME = '{views}'";
            using (OracleConnection b = new OracleConnection(connectionString))
            {
                b.Open();
                using (OracleCommand command = new OracleCommand(p, b))
                {
                    i = Convert.ToInt32(command.ExecuteScalar());

                }
            }
            if (i == 0)
            {
                p = $"CREATE TABLE {views} AS SELECT * FROM TRANSACTION_HISTORY WHERE 1 = 0";

                using (OracleConnection a = new(connectionString))
                {
                    // 打开数据库连接
                    a.Open();

                    using (OracleCommand q = new OracleCommand(p, a))
                    {
                        // MessageBox.Show(p);
                        q.ExecuteNonQuery();
                    }

                }
            }
            else
            {
                p = $"DELETE FROM {views}";
                using (OracleConnection a = new(connectionString))
                {
                    // 打开数据库连接
                    a.Open();
                    using (OracleCommand q = new OracleCommand(p, a))
                    {
                        q.ExecuteNonQuery();
                    }

                }
            }

            bool startdemand = false;
            bool enddemand = false;
            bool statedemand = false;
            bool IDdemand = false;
            bool typedemand = false;
            DateTime starttime = DateTime.Now;
            DateTime endtime = DateTime.Now;
            // 数据库操作
            string selectQuery = $"INSERT INTO {views} SELECT * FROM TRANSACTION_HISTORY WHERE CREDIT_CARD_NUMBERS = :card ";

            if (type != "")
            {
                typedemand = true;
                selectQuery += " AND TRANSACTION_COMMENT = :type ";
            }
            if (state != "")
            {
                statedemand = true;
                selectQuery += " AND STATE = :thestate ";
            }
            if (ID != "")
            {
                IDdemand = true;
                selectQuery += " AND TRANSACTION_ID = :ID ";
            }
            if (start != "")
            {
                startdemand = true;
                if (!DateTime.TryParse(start, out starttime))
                {
                    describe = "起始时间输入有误";
                    goto error;
                }
                selectQuery += " AND TRANSACTION_TIME >= :starttime ";
                if (end != "")
                {
                    if (!DateTime.TryParse(end, out endtime))
                    {
                        describe = "结束时间输入有误";
                        goto error;
                    }
                }
                enddemand = true;
                selectQuery += " AND TRANSACTION_TIME <= :endtime ";
            }

            try
            {
                using (OracleConnection connection = new(connectionString))
                {
                    // 打开数据库连接
                    connection.Open();

                    using (OracleCommand command = new OracleCommand(selectQuery, connection))
                    {

                        // 执行查询
                        command.Parameters.Add("card", OracleDbType.Varchar2).Value = card;
                        if (typedemand)
                        {
                            command.Parameters.Add("type", OracleDbType.Varchar2).Value = type;
                        }
                        if (statedemand)
                        {
                            command.Parameters.Add("thestate", OracleDbType.Varchar2).Value = state;
                        }
                        if (IDdemand)
                        {
                            command.Parameters.Add("ID", OracleDbType.Varchar2).Value = ID;
                        }
                        if (startdemand)
                        {
                            command.Parameters.Add("starttime", OracleDbType.Date).Value = starttime;
                        }
                        if (enddemand)
                        {
                            command.Parameters.Add("endtime", OracleDbType.Date).Value = endtime;
                        }
                        command.ExecuteNonQuery();
                        return true;
                    }
                }
            }
            catch (OracleException ex)
            {
                // 处理 Oracle 数据库相关异常
                MessageBox.Show($"数据库操作失败：{ex.Message}", "数据库错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
            catch (Exception ex)
            {
                // 处理其他类型的异常
                MessageBox.Show($"发生错误：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
        error:
            MessageBox.Show(describe, "发生错误");
            return false;
        }

        public static bool dropview()
        {
            if (views == "")
                return false;
            string selectQuery = $"DROP TABLE {views}";

            try
            {
                using (OracleConnection connection = new(connectionString))
                {
                    // 打开数据库连接
                    connection.Open();

                    using (OracleCommand command = new OracleCommand(selectQuery, connection))
                    {
                        command.ExecuteNonQuery();
                        views = "";
                        return true;
                    }
                }
            }
            catch (OracleException ex)
            {
                // 处理 Oracle 数据库相关异常
                MessageBox.Show($"数据库操作失败：{ex.Message}", "数据库错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
            catch (Exception ex)
            {
                // 处理其他类型的异常
                MessageBox.Show($"发生错误：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
        }
        public static int count()
        {
            if (views == "")
            {
                return -1;
            }
            //MessageBox.Show("2");
            string selectQuery = $"SELECT COUNT(*) FROM {views}";

            try
            {
                using (OracleConnection connection = new(connectionString))
                {
                    // 打开数据库连接
                    connection.Open();

                    using (OracleCommand command = new OracleCommand(selectQuery, connection))
                    {
                        // 使用 ExecuteScalar 获取查询结果
                        object result = command.ExecuteScalar();
                        if (result != null)
                        {
                            return Convert.ToInt32(result); // 返回结果的整数值
                        }
                        else
                        {
                            return -1; // 如果查询结果为空，则返回 0
                        }
                    }
                }
            }
            catch (OracleException ex)
            {
                // 处理 Oracle 数据库相关异常
                MessageBox.Show($"数据库操作失败：{ex.Message}", "数据库错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return -1;
            }
            catch (Exception ex)
            {
                // 处理其他类型的异常
                MessageBox.Show($"发生错误：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return -1;
            }
        }
        public static DataTable getPage(int pages)
        {
            if (views == "")
            {
                return null;
            }

            DataTable result = new DataTable();
            string selectQuery = @$"SELECT * 
                                    FROM (
                                            SELECT t.*, ROWNUM AS rn
                                            FROM (
                                                   SELECT * 
                                                   FROM {views}
                                                   ORDER BY TRANSACTION_TIME DESC
                                                 ) t
                                             WHERE ROWNUM <= :max)
                                      WHERE rn >= :min";
            //MessageBox.Show(selectQuery);
            try
            {
                using (OracleConnection connection = new(connectionString))
                {
                    // 打开数据库连接
                    connection.Open();


                    using (OracleCommand command = new OracleCommand(selectQuery, connection))
                    {
                        command.Parameters.Add(new OracleParameter("max", OracleDbType.Int32)).Value = pages * 10;
                        command.Parameters.Add(new OracleParameter("min", OracleDbType.Int32)).Value = (pages - 1) * 10 + 1;
                        // 使用 ExecuteScalar 获取查询结果
                        using (OracleDataAdapter adapter = new OracleDataAdapter(command))
                        {
                            // 填充 DataTable
                            adapter.Fill(result);
                            return result;
                        }
                    }
                }
            }
            catch (OracleException ex)
            {
                // 处理 Oracle 数据库相关异常
                MessageBox.Show($"数据库操作失败：{ex.Message}", "数据库错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return null;
            }
            catch (Exception ex)
            {
                // 处理其他类型的异常
                MessageBox.Show($"发生错误：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return null;
            }
        }
    }
}
